using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace LiveStock.dataAccess
{
    /// <summary>
    /// Data Access Layer class for Keywords table
    /// </summary>
    public class KeywordsData
    {
        private KeywordsData() { }

        public static int KeywordDeleteByID(int keywordID)
        {
            int affectedRows = 0;
            using (SqlCommand cmd = DBManager.GetCommand("KeywordDeleteByID"))
            {
                cmd.Parameters.AddWithValue("KeywordID", keywordID);
                affectedRows = DBManager.ExecuteNonQuery(cmd);
            }
            return affectedRows;
        }

        public static int KeywordInsert(Word kr)
        {
            int affectedRows = 0;
            using (SqlCommand cmd = DBManager.GetCommand("KeywordInsert"))
            {
                cmd.Parameters.AddWithValue("KeyWord", kr.KeyWord);
                cmd.Parameters.AddWithValue("Weight", kr.Weight);
                cmd.Parameters.AddWithValue("UserID", kr.UserID);
                cmd.Parameters.AddWithValue("DefaultWeight", kr.DefaultWeight);
                affectedRows = DBManager.ExecuteNonQuery(cmd);
            }
            return affectedRows;
        }

        public static Keywords.KeywordsDataTable KeywordsGetAll()
        {
            Keywords.KeywordsDataTable kdt = new Keywords.KeywordsDataTable();
            using (SqlCommand cmd = DBManager.GetCommand("KeywordsGetAll"))
            {
                DBManager.FillDataTable(kdt, cmd);
            }
            return kdt;
        }

        public static int KeywordUpdate(Word kr)
        {
            int affectedRows = 0;
            using (SqlCommand cmd = DBManager.GetCommand("KeywordUpdate"))
            {
                cmd.Parameters.AddWithValue("KeywordID", kr.KeyWordID);
                cmd.Parameters.AddWithValue("KeyWord", kr.KeyWord);
                cmd.Parameters.AddWithValue("Weight", kr.Weight);
                cmd.Parameters.AddWithValue("UserID", kr.UserID);
                affectedRows = DBManager.ExecuteNonQuery(cmd);
            }
            return affectedRows;
        }

        public static Boolean WordAlreadySaved(string isWord, int userid)
        {
            Keywords.KeywordsDataTable kdt = new Keywords.KeywordsDataTable();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Keywords WHERE KeyWord = '" + isWord + "'AND UserID = '" + userid + "' OR KeyWord = '" + isWord + "' AND UserID = '1'"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(kdt, cmd);
            }
            
            if (kdt.Rows.Count == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static void newUserDefaultKeywordsInsert(int userID)
        {
            Keywords.KeywordsDataTable kdt = new Keywords.KeywordsDataTable();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Keywords WHERE UserID = '-1' ORDER BY KeyWord ASC"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(kdt, cmd);
            }

            for (int i = 0; i < kdt.Rows.Count; i++)
            {
                using (SqlCommand cmd = DBManager.GetCommand("KeywordInsert"))
                {
                    cmd.Parameters.AddWithValue("KeyWord", kdt[i].KeyWord);
                    cmd.Parameters.AddWithValue("Weight", kdt[i].Weight);
                    cmd.Parameters.AddWithValue("UserID", userID);
                    cmd.Parameters.AddWithValue("DefaultWeight", kdt[i].DefaultWeight);
                    DBManager.ExecuteNonQuery(cmd);
                }
            }
        }

        public static void restoreDefaultKeywords(int userID)
        {
            Keywords.KeywordsDataTable kdt = new Keywords.KeywordsDataTable();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Keywords WHERE UserID = '"+userID+"'"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(kdt, cmd);
            }

            for (int i = 0; i < kdt.Rows.Count; i++)
            {
                KeywordDeleteByID(kdt[i].KeyWordID);
            }

            newUserDefaultKeywordsInsert(userID);

        }

        public static Keywords.KeywordsDataTable KeywordsGetAll(int userid)
        {
            Keywords.KeywordsDataTable kdt = new Keywords.KeywordsDataTable();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Keywords WHERE UserID = '" + userid + "' ORDER BY KeyWord ASC"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(kdt, cmd);
            }
            return kdt;
        }

        public static Keywords.KeywordsDataTable KeywordsByKewWordID(int keyid)
        {            
            Keywords.KeywordsDataTable kdt = new Keywords.KeywordsDataTable();

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM Keywords WHERE KeyWordID = '" + keyid +"'"))
            {
                SqlConnection conn = DBManager.GetConnection();
                cmd.Connection = conn;
                DBManager.FillDataTable(kdt, cmd);
            }
            
            if (kdt.Rows.Count == 1)
            {
                return kdt;
            }
            else
            {
                return null;
            }
        }
    }
}
